package org.web.base.helper.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.web.base.domain.ResultDO;
import org.web.base.domain.exception.ResultMessageEnum;
import org.web.base.domain.helper.ClassHelper;
import org.web.base.domain.helper.ListHelper;
import org.web.base.domain.helper.ServiceExceptionHelper;
import org.web.base.helper.BeanConvertHelper;
import org.web.base.helper.StringHelper;

import java.io.ByteArrayOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;
import java.util.stream.Collectors;

public class DownloadHelper {


    public static byte[] exportData2Byte(List<OneTask> queryTaskList, List<Map<String, String>> attributeList, ExcelOperator excelOperator, boolean isInterruptWhenException) throws Exception {
        Workbook workbook = exportDataToExcel(queryTaskList, attributeList, excelOperator, false);
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        return outputStream.toByteArray();
    }

    public static <T> Workbook exportDataToExcel(List<OneTask> queryTaskList, List<Map<String, String>> attributeList, ExcelOperator excelOperator, boolean isInterruptWhenException) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        List<KeyValue> keyValueList = extractKeyValues(attributeList);
        LinkedList<String> attributeNameList = keyValueList.stream().map(KeyValue::getKey).collect(Collectors.toCollection(LinkedList::new));
        LinkedList<String> labelNameList = keyValueList.stream().map(KeyValue::getValue).collect(Collectors.toCollection(LinkedList::new));
        for (OneTask oneTask : queryTaskList) {
            String taskMark = oneTask.getTaskMark();
            Sheet dataSheet = workbook.createSheet(taskMark);
            ResultDO resultDO = oneTask.getResultDO();
            int headerRowNum = 0;
            if (resultDO.isSuccess()) {
                List<T> dataList = (List<T>) resultDO.getDomain();
                List<LinkedList<String>> transferDataList = ListHelper.convertObjectList2StringList(dataList, attributeNameList);
                labelNameList.addFirst("名称");// 第一行
                attributeNameList.addFirst("属性名称");// 第二行
                addNumbering(transferDataList);

                ExcelHelper.writeRow(dataSheet, 0, labelNameList);
                ExcelHelper.writeRow(dataSheet, 1, attributeNameList);
                int beginRowNum = 2;
                for (List oneRecord : transferDataList) {
                    ExcelHelper.writeRow(dataSheet, beginRowNum++, oneRecord);
                }
            } else {
                if (isInterruptWhenException) {
                    throw ServiceExceptionHelper.buildServiceException(resultDO);
                }
                String errorMsg = resultDO.getMessage();
                dataSheet.createRow(0).createCell(0).setCellValue(errorMsg);
            }
        }

        Sheet querySheet = workbook.createSheet("DownloadQuery");
        List<Field> queryFields = ClassHelper.getCachedFields(Class.forName(excelOperator.getQueryClassName())).values().stream()
                .sorted(Comparator.comparing(Field::getName)) // 按名称排序（可选）
                .collect(Collectors.toList());
        LinkedList<String> fieldNameList = queryFields.stream().map(Field::getName).collect(Collectors.toCollection(LinkedList::new));
        Class clazz = Class.forName(excelOperator.getQueryClassName());

        List<Object> queryObjectList = queryTaskList.stream().map(OneTask::getQueryObject).collect(Collectors.toList());
        List<LinkedList<String>> queryDataList = ListHelper.convertObjectList2StringList(queryObjectList, fieldNameList);

        fieldNameList.addFirst("查询条件");// 第一行
        fieldNameList.addLast("总记录数");
        ExcelHelper.writeRow(querySheet, 0, fieldNameList);

        int rowNum = 0;
        for (LinkedList<String> oneRecord : queryDataList) {
            OneTask oneTask = queryTaskList.get(rowNum);
            String taskMark = oneTask.getTaskMark();
            oneRecord.addFirst(taskMark);
            List<Object> headPartsFieldNameList = new ArrayList<>();
            headPartsFieldNameList.add(taskMark);
            Object queryObject = oneTask.getQueryObject();
            Row row = querySheet.createRow(rowNum+1);
            String message;
            ResultDO resultDO = oneTask.getResultDO();
            if (resultDO.isSuccess()) {
                // 如果成功，则返回总记录数。
                message = String.valueOf(resultDO.getTotalCount());
            } else {
                message = resultDO.getMessage();
                if (StringUtils.isNotBlank(message)) {
                    // 在 Excel 中，每个单元格的字符限制是 32,767 个字符。这意味着你可以在单元格中输入或粘贴的最大字符数是 32,767 个字符。
                    // 虽然可以输入多达 32,767 个字符，但单元格中直接显示的字符数限制为 1,024 个字符
                    message = StringHelper.truncateString(message, 1024);
                } else {
                    message = "查询失败";
                }
            }
            oneRecord.addLast(message);
            ExcelHelper.writeRow(querySheet, rowNum + 1, oneRecord);
            rowNum++;
        }
        return workbook;
    }





    /**
     * 从每个仅含一个键值对的 Map 中提取 key，并收集成一个 List。
     *
     * @param mapList 包含多个仅含一个 key 的 Map 列表
     * @return 每个 Map 的 key 组成的 List
     */
    public static List<KeyValue> extractKeyValues(List<Map<String, String>> mapList) {
        List<KeyValue> result = new ArrayList<>();
        for (Map<String, String> map : mapList) {
            for (Map.Entry<String, String> entry : map.entrySet()) {
                result.add(new KeyValue(entry.getKey(), entry.getValue()));
            }
        }
        return result;
    }

    /**
     * 给 transferDataList 添加 1、2、3 的标号前缀
     *
     * @param transferDataList 原始数据列表（每行一个LinkedList<String>）
     * @return 添加标号后的新列表
     */
    private static void addNumbering(List<LinkedList<String>> transferDataList) {
        for (int i = 0; i < transferDataList.size(); i++) {
            LinkedList<String> original = transferDataList.get(i);
            original.addFirst(String.valueOf((i + 1))); // 添加前缀标号
        }
    }

    /**
     * 将 Excel sheet 转换为  List<OneRowDTO>，并根据 clazz 过滤没有 set 方法的列。
     * <p>
     * 首先描述excel的格式：
     * 1)第一行是表头，是clazz的属性名，第二行开始是数据。
     * 2)如果表头中包含中文【序号】、【行标识】，说明存在行标识，需要将其作为数据行的唯一标识。即OneRowDTO 的rowMark属性。且不允许为空。
     *
     * @param sheet Excel的Sheet
     * @param clazz 目标对象的Class类型，用于过滤表头
     * @return List<OneRowDTO> 转换后的对象列表。
     */
    public static List<OneRowDTO> sheetToListMap(Sheet sheet, Class<?> clazz) {
        List<OneRowDTO> list = new ArrayList<>();

        // 获取表头行（第一行）
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            return list;  // 如果没有表头，直接返回空列表
        }

        // 获取表头的列名，并根据 clazz 过滤掉没有 set 方法的列
        Map<String, Method> setters = BeanConvertHelper.getSettersByClass(clazz);
        Map<Integer, String> columnIndexToHeader = new HashMap<>();  // 用于存储列索引与表头名的对应关系
        int rowMarkIndex = -1;  // 用于记录行标识所在的列索引
        for (Cell cell : headerRow) {
            if (cell != null) {
                String header = cell.getStringCellValue();
                if (StringUtils.isNotEmpty(header)) {
                    if (setters.containsKey(header.toLowerCase())) {
                        columnIndexToHeader.put(cell.getColumnIndex(), header);  // 记录列索引与表头的关系
                    }
                    if (header.equalsIgnoreCase("rowMark") || header.equalsIgnoreCase("rowNum") || header.equalsIgnoreCase("序号") || header.equalsIgnoreCase("行号")) {
                        rowMarkIndex = cell.getColumnIndex();  // 记录行标识所在的列索引
                    }
                }
            }
        }

        // 校验重复
        Set<String> rowMarkSet = new HashSet<>();
        // 遍历数据行
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            OneRowDTO oneRowDTO = new OneRowDTO();
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;  // 跳过空行
            }
            if (rowMarkIndex != -1) {
                Cell rowMarkCell = row.getCell(rowMarkIndex);
                // 检查行标识列是否为空或值是否为空.
                if (rowMarkCell == null || StringUtils.isEmpty(getCellValueAsString(rowMarkCell))) {
                    throw ServiceExceptionHelper.buildServiceException(ResultMessageEnum.PARAM_EMPTY, "第" + (i + 1) + "行，行标识不能为空。");
                }
                String rowMark = getCellValueAsString(rowMarkCell).trim();
                if (rowMarkSet.contains(rowMark)) { // 检查行标识是否重复
                    throw ServiceExceptionHelper.buildServiceException(ResultMessageEnum.DATA_DUPLICATE, "第" + (i + 1) + "行，行标识重复。");
                } else {
                    rowMarkSet.add(rowMark);
                }
                oneRowDTO.setRowMark(rowMark);
            }

            Map<String, Object> rowDataMap = new HashMap<>();
            // 遍历每列，根据列索引从表头映射关系中取值，将数据放入 map 中
            for (Map.Entry<Integer, String> entry : columnIndexToHeader.entrySet()) {
                int columnIndex = entry.getKey();
                String header = entry.getValue();

                Cell cell = row.getCell(columnIndex);
                String cellValue = getCellValueAsString(cell);
                if (StringUtils.isNotEmpty(cellValue)) {
                    rowDataMap.put(header, cellValue.trim());
                }
            }
            oneRowDTO.setRowDataMap(rowDataMap);
            list.add(oneRowDTO);
        }

        return list;
    }

    /**
     * 将 Cell 的值转换为 String
     *
     * @param cell Excel的单元格
     * @return 转换后的 String 值
     */
    public static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 如果是日期，返回日期字符串
                    return cell.getDateCellValue().toString();
                } else {
                    // 判断是否为整数
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == (long) numericValue) {
                        // 整数格式，去掉小数点
                        return String.valueOf((long) numericValue);
                    } else {
                        // 小数格式，直接返回
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}

class KeyValue {
    private String key;
    private String value;

    public KeyValue(String key, String value) {
        this.key = key;
        this.value = value;
    }

    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    @Override
    public String toString() {
        return "KeyValue{key='" + key + "', value='" + value + "'}";
    }
}


























